Imports MyClasses
Imports System.Data
Imports System.Data.SqlClient

Public Class DTax

    Public Shared Sub Save(ByRef TaxGrid As Windows.Forms.DataGridView, ByVal ItemID As Integer)
        'Saves Taxes can Applied to Item in tItemTax
        Dim strSql As String
        Dim intRowCount As Integer
        Dim param(2) As SqlParameter
        Try

            For intRowCount = 0 To TaxGrid.Rows.Count - 1
                strSql = "INSERT INTO tItemTax(ItemID,TaxID,Amount) " & _
                         "VALUES (@ItemID,@TaxID,@Amount)"
                param(0) = New SqlParameter("@ItemID", ItemID)
                param(1) = New SqlParameter("@TaxID", TaxGrid.Item(0, intRowCount).Value.ToString)
                param(2) = New SqlParameter("@Amount", TaxGrid.Item(3, intRowCount).Value.ToString)

                DbHelper.ExecuteNonQuery(strSql, param)

            Next
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DTax", "Save")
        End Try
    End Sub

    'Public Shared Sub Save(ByRef TaxGrid As C1FlexGrid, ByVal BillID As Integer)
    'Saves Taxes can Applied to Item in tItemTax
    'DTax.Save()
    ' End Sub

    Public Shared Function Search(ByVal TaxID As Integer) As DataSet
        Dim strSearch As String
        Try
            strSearch = "SELECT TaxName,Descr,Amount,ApplyTo " & _
                        "FROM mTax " & _
                        "WHERE TaxID = " & TaxID
            Return DbHelper.ExecuteDataSet(strSearch)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DTax", "Search")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetApplyTo(ByVal TaxName As String) As DataTable
        Dim strSql As String
        Try
            strSql = "SELECT ApplyTo,Amount " & _
                        "FROM mTax " & _
                        "WHERE TaxName = '" & TaxName & "'"
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DTax", "GetApplyTo")
            Return Nothing
        End Try
    End Function

    Public Shared Sub Save(ByVal Tax As CTax)
        'Saves New Tax Entry in Mtax
        Dim strSql As String
        Dim param(5) As SqlParameter

        Try
            strSql = "INSERT INTO mTax(TaxID,TaxName,Descr,Amount,LU,LD) " & _
                     "VALUES (@TaxID,@TaxName,@Descr,@Amount,@LU,@LD)"
            param(0) = New SqlParameter("@TaxID", Tax.TaxID)
            param(1) = New SqlParameter("@TaxName", Tax.TaxName)
            param(2) = New SqlParameter("@Descr", Tax.TaxDescription)
            param(3) = New SqlParameter("@Amount", Tax.TaxAmount)
            param(4) = New SqlParameter("@LU", CCommon.GetUserID())
            param(5) = New SqlParameter("@LD", Date.Now())

            DbHelper.ExecuteNonQuery(strSql, param)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DTax", "Save")
        End Try
    End Sub
    Public Shared Function Save(ByRef tax As CTax, ByVal SaveUpdate As String) As Boolean
        Dim sql As String
        Dim param(6) As SqlParameter
        Try
            If SaveUpdate = "S" Then
                sql = "INSERT INTO mTax(TaxID,TaxName,Descr,Amount,ApplyTo,LU,LD) " & _
                         "VALUES (@TaxID,@TaxName,@Descr,@Amount,@ApplyTo,@LU,@LD)"
                param(0) = New SqlParameter("@TaxID", DGeneral.GetSeqNum("TAX", True))
            Else
                sql = "update mtax set TaxName=@TaxName,Descr=@Descr," & _
                      "Amount=@Amount,ApplyTo=@ApplyTo,LU=@LU,LD=@LD where TaxID=@TaxID"
                param(0) = New SqlParameter("@TaxID", tax.TaxID)
            End If

            param(1) = New SqlParameter("@TaxName", tax.TaxName)
            param(2) = New SqlParameter("@Descr", tax.TaxDescription)
            param(3) = New SqlParameter("@Amount", tax.TaxAmount)
            param(4) = New SqlParameter("@ApplyTo", tax.ApplyTo)
            param(5) = New SqlParameter("@LU", CCommon.GetUserID)
            param(6) = New SqlParameter("@LD", Date.Now)
            Return DbHelper.ExecuteNonQuery(sql, param)

            'sql = "Delete from itemtax where ItemID = @ItemID"

            'param(0) = New SqlParameter("@ItemID", Item.ItemID)

            'Return DbHelper.ExecuteNonQuery(sql, param)

            'sql = "INSERT INTO tItemTax(ItemID,TaxID,Amount " & _
            '      "VALUES(@ItemID,@TaxID,@Amount)"

            'param(0) = New SqlParameter("@ItemID", Item.ItemID)
            'param(1) = New SqlParameter("@TaxID", Item)
            'param(2) = New SqlParameter("@Amount", Item.ItemID)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DTax", "Save")
            Return 0
        End Try
    End Function

    Public Shared Function GetTaxDeatils() As DataTable
        Dim strSql As String
        Dim dbcon As New dbAccess
        Try
            strSql = "SELECT TaxName,Descr,Amount,ApplyTo " & _
                        "FROM mTax "
            Return dbcon.ExecuteDataTable(strSql)
        Catch ex As Exception
            Return Nothing
            DGeneral.ErrorHandler(ex.Message, "DAL", "DTax", "GetTaxDeatils", strSql)
        End Try
    End Function

    Public Shared Function GetTax(ByVal ItemID As Integer) As DataTable
        Dim StrSql As String = ""
        Dim Result As Integer
        Try
            StrSql = "SELECT  COUNT(TaxID) AS TaxID FROM tItemTax" & _
                     " WHERE ItemID =" & ItemID
            Result = DbHelper.ExecuteScalar(StrSql)
            If Result > 0 Then
                StrSql = " SELECT mTax.TaxID,TaxName AS Name,tItemTax.Amount AS Rate,'0' AS Amount" & _
                         " FROM mTax, tItemTax WHERE mTax.TaxID = tItemTax.TaxID AND ItemID=" & ItemID & " AND ApplyTo in ('Both','Purchase') " & _
                         " UNION " & _
                         " SELECT mTax.TaxID,TaxName AS Name,'0' AS Rate,'0' AS Amount FROM mTax,tItemTax " & _
                         " WHERE mTax.TaxID NOT IN (SELECT TaxID FROM tItemTax WHERE itemid =" & ItemID & " AND ApplyTo in ('Both','Purchase'))"
            Else
                StrSql = "SELECT mTax.TaxID,TaxName AS Name,'0'  AS Rate,'0' AS Amount FROM mTax WHERE ApplyTo in ('Both','Purchase')"
            End If
            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DTax", "GetTax", StrSql)
            Return Nothing
        End Try
    End Function

    '_20080304 : Add Overloaded function for TAX for SALE on PI
    Public Shared Function GetTax(ByVal ItemID As Integer, ByVal CLRcode As String) As DataTable
        Dim StrSql As String = String.Empty
        Dim Result As Integer
        Try
            StrSql = "SELECT  COUNT(TaxID) AS TaxID FROM tItemTax" & _
                     " WHERE ItemID =" & ItemID
            Result = DbHelper.ExecuteScalar(StrSql)
            If Result > 0 Then
                StrSql = " SELECT mTax.TaxID, TaxName AS Name, tItemTax.Amount AS Rate, '0' AS Amount, ApplyTo" & _
                         " FROM mTax, tItemTax WHERE mTax.TaxID = tItemTax.TaxID AND ItemID=" & ItemID & " AND ApplyTo in ('Both', 'Purchase', 'Sale', 'SaleExcl') " & _
                         " UNION " & _
                         " SELECT mTax.TaxID, TaxName AS Name, '0' AS Rate, '0' AS Amount, ApplyTo FROM mTax, tItemTax " & _
                         " WHERE mTax.TaxID NOT IN (SELECT TaxID FROM tItemTax WHERE itemid =" & ItemID & " AND ApplyTo in ('Both', 'Purchase', 'Sale', 'SaleExcl'))"
            Else
                StrSql = " SELECT mTax.TaxID, TaxName AS Name, '0' AS Rate, '0' AS Amount, ApplyTo FROM mTax WHERE ApplyTo in ('Both', 'Purchase', 'Sale', 'SaleExcl')"
            End If
            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DTax", "GetTax", StrSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetDefaultTaxID() As DataTable
        Dim StrSql As String = String.Empty
        Try
            StrSql = " SELECT '0' as CodeID,'' as CodeDesc FROM mmisccd  " & _
                     " UNION " & _
                     " SELECT CodeID,CodeDesc FROM mmisccd WHERE Status='A' AND Codetype='APPLYTO'  " & _
                     " ORDER BY CodeID "
            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DTax", "GetDefaultTaxID", StrSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetTaxID(ByVal TaxName As String) As Integer
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT TaxID FROM mTax WHERE TaxName='" & TaxName & "'"
            Return DbHelper.ExecuteScalar(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DTax", "GetTaxID", StrSql)
            Return 0
        End Try
    End Function

    Public Shared Function IsTaxUsed(ByVal TaxID As Integer) As Integer
        Try
            If (DbHelper.ExecuteScalar("SELECT TaxID FROM tItemTax WHERE TaxID=" & TaxID)) > 0 Then
                Return 1
            ElseIf (DbHelper.ExecuteScalar("SELECT TaxID FROM tBillItemTax WHERE TaxID=" & TaxID)) > 0 Then
                Return 1
            Else
                Return 0
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DTax", "IsTaxUsed")
            Return 0
        End Try
    End Function

    Public Shared Function DeleteTax(ByVal TaxID As Integer) As Integer
        Try
            Return DbHelper.ExecuteNonQuery(" DELETE FROM mTax WHERE TaxID=" & TaxID & "")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DTax", "DeleteTax")
            Return 0
        End Try
    End Function

    Public Shared Function GetSaleTaxValue(ByVal ItemID As Long, ByVal CLRcode As String) As Double
        'FUNCTION WILL RETURN THE SUMMATION OF ALL THE TAXES WHICH ARE APPLIED EXCLUSIVE TAXES TO THIS ITEM & BATCH
        'IF NO TAX IS APPLIED THEN RETURN 0 IMP
        Dim strSql As String
        Try
            strSql = "SELECT ISNULL(SUM( TaxVal),0) AS TAX" & _
                     " FROM tSaleTax WHERE ItemID=" & ItemID & " AND  CLRcode='" & CLRcode & "' " & _
                     " AND Type='E'"
            Return DbHelper.ExecuteScalar(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DTax", "GetSaleTaxValue")
            Return 0
        End Try
    End Function

    Public Shared Function GetItemTaxes(ByVal ItemID As Long, ByVal CLRcode As String, ByVal Amount As Double) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT  TaxID AS ID ,TaxVal AS Val  ,Type, 0.0 AS Amount " & _
                     "FROM tSaleTax WHERE ItemID=" & ItemID & " AND  CLRcode='" & CLRcode & "' " & _
                     "Order by Type"

            Dim dtbTax As DataTable = DbHelper.ExecuteDataTable(strSql)
            strSql = ""
            If dtbTax.Rows.Count > 0 Then
                Dim dblInclTax As Double = 0
                Dim dblExclTax As Double = 0

                For intCount As Integer = 0 To dtbTax.Rows.Count - 1
                    With dtbTax.Rows(intCount)
                        If .Item("Type") = "I" Then
                            dblInclTax += .Item("Val")
                        ElseIf .Item("Type") = "E" Then
                            dblExclTax += .Item("Val")
                        End If
                    End With
                Next
                Dim dblExTotal As Double = (dblExclTax * (100 + dblInclTax)) / 100
                dblExTotal = (dblExTotal * Amount) / (100 + dblExclTax + dblInclTax)
                Dim dblIncTotal As Double = (dblInclTax * (Amount - dblExTotal)) / (100 + dblInclTax)

                For intCount As Integer = 0 To dtbTax.Rows.Count - 1
                    With dtbTax.Rows(intCount)
                        If .Item("Type") = "I" Then
                            .Item("Amount") = (dblIncTotal * .Item("Val")) / dblInclTax
                        ElseIf .Item("Type") = "E" Then
                            .Item("Amount") = (dblExTotal * .Item("Val")) / dblExclTax
                        End If
                    End With
                Next

            Else : Return Nothing
            End If
            Return dtbTax
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DTax", "GetItemTaxes", strSql)
            Return Nothing
        End Try

    End Function
End Class
